﻿using System;
using System.Collections;
using System.Linq;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using SQLServices.Repository;

namespace SQLRunnerSMO
{
    public class SqlServerSmo: ISQLConnection
    {
        public bool LoginSecure { get; private set; }
        public string ServerName { get; private set; }
        public string UserId { get; private set; }
        public string Password { get; private set; }
        public int VersionMajor { get { return SqlServer.Information.Version.Major;  } }

        internal Server SqlServer;
        private string DatabaseName;
        private SqlConnSmo MainConn;

        /// <summary>
        /// Opens a connection to the server with the passed parameters
        /// </summary>
        public void OpenConnection(string serverName, string userId, string password, bool trusted)
        {            
            LoginSecure = trusted;
            ServerName = serverName;
            UserId = userId;
            Password = password;
            DatabaseName = string.Empty;
            Reset();            
        }

        /// <summary>
        /// Creates a new instance of ISQLDBConn if there is not database connection or if the current
        /// database is different to the passed database name
        /// </summary>
        public ISQLDBConn GetDatabase(string databaseName)
        {
            if ( SqlServer == null)
                throw new ApplicationException("Connection to the SQL Server was " +
                    "not established, please call the 'OpenConnection' method " +
                    "before this method is executed");
            if ( DatabaseName != string.Empty  && DatabaseName.Equals(databaseName)) return MainConn;
            DatabaseName = databaseName;            
            Reset();
            MainConn = new SqlConnSmo(this);
            return MainConn;          
        }        

        /// <summary>
        /// Gets the current database connection
        /// </summary>
        /// <returns></returns>
        public ISQLDBConn GetDatabase()
        {
            if ( DatabaseName == string.Empty )               
            	throw new ApplicationException("Internal database has not been initialise, " +
				"please use the overloaded method indicating the database name to retrieve");

            return GetDatabase(DatabaseName);
        }

        /// <summary>
        /// It calles the Disconnect method and creates a new connection to the server
        /// </summary>
        public void Reset()
        {
            Disconnect();

            var conn = new ServerConnection(ServerName)
                           {
                               ApplicationName = "SQLRunner",
                               DatabaseName = DatabaseName,
                               AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect,
                               NonPooledConnection = true,
                               LoginSecure = LoginSecure
                           };

            if ( !LoginSecure )
            {
                conn.Login = UserId;
                conn.Password = Password;
            }
            
            SqlServer = new Server(conn);
            
        }

        /// <summary>
        /// Disconnects and close the current connection to the server
        /// </summary>
        public void Disconnect()
        {
            if (SqlServer == null) return;        
            SqlServer.ConnectionContext.Disconnect();
            SqlServer = null;         
        }

        /// <summary>
        /// Returns the list of available database for the current server
        /// </summary>
        public ArrayList ListOfDatabases()
        {
            if ( SqlServer == null ) return null;
            var databases =
                from database in SqlServer.Databases.OfType<Database>()
                select database.Name;

            return new ArrayList(databases.ToArray());
        }
    }
}
